//202102104015柴亚楠
package cn.edu.sdjzu.xg.kcsj.dao;

import cn.edu.sdjzu.xg.kcsj.domain.Classroom;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class ClassroomDao {
    private static ClassroomDao classroomDao = new ClassroomDao();
    private ClassroomDao(){}
    public static ClassroomDao getInstance(){
        return classroomDao;
    }
    public Collection<Classroom> findAll ( Pagination pagination,List<Condition> conditionList_json_str,Connection connection ) throws SQLException {
        Collection<Classroom>desiredClassrooms = new HashSet<Classroom>();
        StringBuilder sql_select  = new StringBuilder ("SELECT * FROM Classroom");
        if (conditionList_json_str != null){
            String whereClause = Condition.toWhereClause (conditionList_json_str);
            sql_select.append (whereClause);
        }
        int totalNum = classroomDao.getInstance ().count(connection);
        if (pagination != null){
            sql_select.append (pagination.toLimitClause (totalNum)).append (" ");
        }
        PreparedStatement preparedStatement = connection.prepareStatement (sql_select.toString ());
        ResultSet resultSet = preparedStatement.executeQuery ();
        while (resultSet.next ()){
            int id = resultSet.getInt ("id");
            String classroomNo = resultSet.getString ("no");
            String classLocation = resultSet.getString ("location");
            Classroom desiredClassroom = new Classroom (id,classroomNo,classLocation);
            desiredClassroom.setId (id);
            desiredClassrooms.add (desiredClassroom);
        }
        JdbcHelper.close (resultSet,preparedStatement);
        return desiredClassrooms;
    }

    public Classroom find (int id,Connection connection) throws SQLException {
        Classroom desiredClassroom = null;
        PreparedStatement pstmt_select_id = connection.prepareStatement("SELECT * FROM Classroom" + " WHERE id = ?");
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        while(resultSet_select_id.next()){
            String classroomNo = resultSet_select_id.getString("no");
            String classLocation = resultSet_select_id.getString("location");
            desiredClassroom = new Classroom (id,classroomNo,classLocation);
        }
        JdbcHelper.close(resultSet_select_id,pstmt_select_id);
        return desiredClassroom;
    }
    private int count (Connection connection) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement ("SELECT * FROM Classroom");
        ResultSet resultSet = preparedStatement.executeQuery ();
        int r = 0;
        while (resultSet.next ()){
            r++;
        }
        return r;
    }
    public boolean add (Classroom classroom,Connection connection) throws SQLException {
        String add_sql = "INSERT INTO Classroom(no,location) VALUES (?,?)";
        PreparedStatement ps = connection.prepareStatement (add_sql);
        ps.setString (1,classroom.getNo());
        ps.setString (2,classroom.getLocation ());
        int add_affectedRowNums = ps.executeUpdate ();
        JdbcHelper.close (ps,null);
        return add_affectedRowNums > 0;
    }

    public boolean delete (int id,Connection connection) throws SQLException {
        String delect_sql = "DELETE FROM Classroom WHERE id = ?";
        PreparedStatement p = connection.prepareStatement (delect_sql);
        p.setInt (1,id);
        int delete_affectedRowNums = p.executeUpdate ();
        JdbcHelper.close (p,null);
        return delete_affectedRowNums > 0;
    }

    public boolean update (Classroom classroom,Connection connection) throws SQLException {
        String update_sql = "UPDATE Classroom SET no=?, location=? WHERE id=?";
        PreparedStatement ps = connection.prepareStatement (update_sql);
        ps.setString (1,classroom.getNo ());
        ps.setString (2,classroom.getLocation ());
        ps.setInt (3,classroom.getId ());
        int update_affectedRowNums= ps.executeUpdate ();
        JdbcHelper.close (ps,null);
        return update_affectedRowNums > 0;
    }
}
